import pandas as pd
import numpy as np
from openpyxl import load_workbook
from itertools import product
from .diff_records import SheetAdded, SheetDeleted, CellDiff
from app.error import ExcelProcessingError
import warnings

warnings.filterwarnings(
    "ignore", category=UserWarning, module="openpyxl.styles.stylesheet"
)


class ExcelComparator:
    """Excel文件比较工具类"""

    def __init__(self, file1: str, file2: str):
        self.file1 = file1
        self.file2 = file2
        self._diff_records = []
        try:
            self.wb1 = load_workbook(file1)
        except Exception as e:
            raise ExcelProcessingError(f"无法加载Excel文件 '{file1}': {str(e)}", 400)
        try:
            self.wb2 = load_workbook(file2)
        except Exception as e:
            raise ExcelProcessingError(f"无法加载Excel文件 '{file2}': {str(e)}", 400)
        self.sheets1 = set(self.wb1.sheetnames)
        self.sheets2 = set(self.wb2.sheetnames)

    def compare(self) -> list:
        """执行比较并返回差异结果"""
        self._compare_sheets()
        for sheet in self.sheets1 & self.sheets2:
            self._compare_sheet_cells(sheet)

        return [record.to_dict() for record in self._diff_records]

    def _compare_sheets(self):
        """处理sheet新增/删除情况"""
        for sheet in self.sheets2 - self.sheets1:
            self._diff_records.append(SheetAdded(sheet))

        for sheet in self.sheets1 - self.sheets2:
            self._diff_records.append(SheetDeleted(sheet))

    def _compare_sheet_cells(self, sheet_name: str):
        """比较指定工作表的单元格差异"""
        df1 = pd.read_excel(
            self.file1, sheet_name=sheet_name, header=None, engine="openpyxl"
        )
        df2 = pd.read_excel(
            self.file2, sheet_name=sheet_name, header=None, engine="openpyxl"
        )

        max_row = max(df1.shape[0], df2.shape[0])
        max_col = max(df1.shape[1], df2.shape[1])

        for row, col in product(range(max_row), range(max_col)):
            val1 = self._safe_get_value(df1, row, col)
            val2 = self._safe_get_value(df2, row, col)
            diff_type = self._determine_diff_type(val1, val2)
            if not diff_type:
                continue
            self._diff_records.append(
                CellDiff(
                    sheet_name=sheet_name,
                    row=row,
                    col=col,
                    old_val=val1,
                    new_val=val2,
                    diff_type=diff_type,
                )
            )

    def _safe_get_value(self, df: pd.DataFrame, row: int, col: int) -> object:
        """安全获取DataFrame中的值"""
        try:
            return (
                df.iloc[row, col] if row < df.shape[0] and col < df.shape[1] else np.nan
            )
        except:
            return np.nan

    def _determine_diff_type(self, val1: object, val2: object) -> str or None:
        """判断单元格差异类型"""
        if pd.isna(val1) and pd.isna(val2):
            return None
        elif pd.isna(val1):
            return "新增单元格"
        elif pd.isna(val2):
            return "删除单元格"
        elif val1 != val2:
            return "值不同"
        return None
